package com.bj58.ecdata.mailreport.controllers;

import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Timer;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.web.bind.ServletRequestUtils;

import com.bj58.ecdata.mailreport.cache.ServiceBeanFactory;
import com.bj58.ecdata.mailreport.model.IEAdd;
import com.bj58.ecdata.mailreport.model.IEMain;
import com.bj58.ecdata.mailreport.model.IEMainSrc;
import com.bj58.ecdata.mailreport.model.IESend;
import com.bj58.ecdata.mailreport.model.IETable;
import com.bj58.ecdata.mailreport.model.IETableSearch;
import com.bj58.ecdata.mailreport.model.IETemplateType;
import com.bj58.ecdata.mailreport.model.IRDbConfig;
import com.bj58.ecdata.mailreport.model.Paginate;
import com.bj58.ecdata.mailreport.service.impl.DbConfigService;
import com.bj58.ecdata.mailreport.service.impl.IEAddService;
import com.bj58.ecdata.mailreport.service.impl.IEMainService;
import com.bj58.ecdata.mailreport.service.impl.IEMainSrcService;
import com.bj58.ecdata.mailreport.service.impl.IESendService;
import com.bj58.ecdata.mailreport.service.impl.IETableSearchService;
import com.bj58.ecdata.mailreport.service.impl.IETableService;
import com.bj58.ecdata.mailreport.service.impl.IETemplateTypeService;
import com.bj58.ecdata.mailreport.service.impl.IntEmailMainService;
import com.bj58.ecdata.mailreport.threads.IESendTimerTask;
import com.bj58.ecdata.mailreport.util.DateUtil;
import com.bj58.ecdata.mailreport.util.MathEval;
import com.bj58.ecdata.mailreport.util.MyPathUtil;
import com.bj58.ecdata.mailreport.util.TemplateMethodFactory;
import com.bj58.ecdata.mailreport.util.VelocityTools;
import com.bj58.groupbuy.action.interceptors.Check;
import com.bj58.groupbuy.action.util.MyServletRequestUtils;
import com.bj58.groupbuy.action.util.SomePublicUtil;
import com.bj58.wf.mvc.ActionResult;
import com.bj58.wf.mvc.BeatContext;
import com.bj58.wf.mvc.BeatContext.Model;
import com.bj58.wf.mvc.MvcController;
import com.bj58.wf.mvc.actionresult.ContentResult;
import com.bj58.wf.mvc.annotation.GET;
import com.bj58.wf.mvc.annotation.POST;
import com.bj58.wf.mvc.annotation.Path;

/**自定义邮件
 * @author sunny3791
 *
 */
@Path("/intemail")
public class IntEmailController extends MvcController{
	private IEMainService IEMainService = (IEMainService) ServiceBeanFactory.get(IEMain.class, IEMainService.class);
	private IESendService IESendService = (IESendService) ServiceBeanFactory.get(IESend.class,IESendService.class);
	private IETableService IETableService = (IETableService) ServiceBeanFactory.get(IETable.class,IETableService.class);
	private IETableSearchService IETableSearchService = (IETableSearchService) ServiceBeanFactory.get(IETableSearch.class,IETableSearchService.class);
	private IEMainSrcService IEMainSrcService = (IEMainSrcService) ServiceBeanFactory.get(IEMainSrc.class,IEMainSrcService.class);
	private IEAddService ieAddService = (IEAddService) ServiceBeanFactory.get(IEAdd.class,IEAddService.class);
	private IETemplateTypeService IETemplateTypeService = (com.bj58.ecdata.mailreport.service.impl.IETemplateTypeService) ServiceBeanFactory.get(IETemplateType.class,IETemplateTypeService.class);
	private IntEmailMainService IntEmailMainService = new IntEmailMainService();
	
	private DbConfigService IRDbConfigService = (DbConfigService) ServiceBeanFactory.get(IRDbConfig.class,DbConfigService.class);
	private SomePublicUtil spu = new SomePublicUtil();
	
	/**自定义邮件管理
	 * @return
	 */
	@Path("manager/{state}")
	@Check("mailreport/intemail/manager/show")
	@POST
	@GET
	public ActionResult ieManage (String state){
		HttpServletRequest request = beat.getRequest();
		Model model= beat.getModel();
		String returnUrl ="";
		TemplateMethodFactory.setVelocityContext("tools", VelocityTools.class, model);
		try {
			if("show".equals(state)){  //管理主页面
				String emailName = MyServletRequestUtils.getStringParameter(request, "emailName","");
				String emailCate = MyServletRequestUtils.getStringParameter(request, "emailCate","");
				int pageIndex = MyServletRequestUtils.getIntParameter(request, "pageindex", 1);
				//查询条件组织 以及分页查询参数组织
				StringBuffer where = new StringBuffer(); //查询条件
				StringBuffer url = new StringBuffer(); //分页查询参数
				where.append(" 1=1  ");
				if(!"".equals(emailName)){
					where.append(" AND emailName like '%"+emailName+"%'");
					url.append("&emailName="+emailName);
				}
				if(!"".equals(emailCate)){
					where.append(" AND  emailCate like '%"+emailCate+"%'");
					url.append("&emailCate="+emailCate);
				}
				
				//分页查询
				Paginate<IEMain> pList = IEMainService.getPagListByCon(pageIndex, 20, where.toString());
				Map<Integer,IESend> sendMap = new HashMap<Integer,IESend>();
				if(pList != null && pList.getItems() != null && pList.getItems().size() > 0){
					
					StringBuffer sb=new StringBuffer("mainId in(");
					for(IEMain main: pList.getItems()){
						sb.append(main.getId()+",");
					}
					sb.deleteCharAt(sb.length()-1);
					sb.append(")");
					List<IESend> sendList=IESendService.getListByCon(sb.toString());
					for(IESend s: sendList){
						if("day".equals(s.getSendFre()))
							s.setSendFre("每天");
						else if("week".equals(s.getSendFre())){
							s.setSendFre("每周一");
						}
						if(s.getState() == 0){
							s.setWarnTel("未调度");
						}else{
							switch(s.getSendState()){
								case 0:s.setWarnTel("未发送");break;
								case 1:s.setWarnTel("成功");break;
								case 2:s.setWarnTel("发送中");break;
								case 3:s.setWarnTel("失败");break;
								case 4:s.setWarnTel("预警");break;
								case 5:s.setWarnTel("待处理");break;
								case 6:s.setWarnTel("等待");break;
								default:s.setWarnTel("未发送");break;
							}
						}
						sendMap.put(s.getMainId(), s);
					}
				}
				model.add("sendMap",sendMap);
				model.add("pList",pList);
				model.add("rsList",pList.getItems());	
				model.add("paramurl",url.toString());
				model.add("emailName",emailName);
				model.add("emailCate",emailCate);
				model.add("pageIndex",pageIndex);
				returnUrl = "intemail/iemanager";
			}else if("changestate".equals(state)){ //状态修改
				try{
					int id = ServletRequestUtils.getIntParameter(request, "id", 0);
					int upstate = ServletRequestUtils.getIntParameter(request, "upstate", -1);
					if(id == 0 || upstate == -1){
						return new ContentResult("error");
					}
					IESend sendVo= IESendService.getByMainId(id);
					if (upstate == 0 || upstate == 1) {
						IEMain vo = IEMainService.getById(id);
						vo.setState(upstate);
						sendVo.setState(upstate);
						if (IEMainService.updateVo(vo)&&IESendService.updateVo(sendVo)) {
							return new ContentResult("success");
						} else {
							return new ContentResult("error");
						}
					}else{
						returnUrl = "intemail/testExecute";
//						if(sendVo==null)
//							return new ContentResult("error");
//						String serchDate=ServletRequestUtils.getStringParameter(request, "serchDate", null);
//						if("".equals(serchDate))
//							serchDate=null;
//						Thread sendThread=new Thread(new IESendTimerTask(sendVo,serchDate));
//						sendThread.start();
//						return new MessageResult("正在执行，请耐心等待，并注意查收邮件!");
					}
				}catch (Exception e) {
					e.printStackTrace();
					return new ContentResult("error");
				}
			}else if("toexecute".equals(state)){
				int id = ServletRequestUtils.getIntParameter(request, "id", 0);
				IEMain mainVo = IEMainService.getById(id);
				model.add("mainVo",mainVo);
				IESend sendVo= IESendService.getByMainId(id);
				model.add("sendVo",sendVo);
				returnUrl = "intemail/testExecute";
			}else if("toeditemail".equals(state)){ //编辑界面
				List<IETemplateType> templateList = IETemplateTypeService.getListByCon(" state = 1");
				model.add("templateList",templateList);
				
				//DB模板
				List<IRDbConfig> dbList = IRDbConfigService.getAllDb();  //数据库列表
				Map<String,Object> conMap = new HashMap<String,Object>();
				conMap.put("dbList", dbList);
				String main = MyPathUtil.getTemByPath(MyPathUtil.getIEPath()+"dbtemplate.html");
				String dbTemplate  = VelocityTools.render(main, conMap);
				model.add("dbTemplate",dbTemplate);
				model.add("dbList",dbList);

				//邮件主体、发送、TABLE配置等
				int mainId = ServletRequestUtils.getIntParameter(request, "id", 0);
				IEMain mainVo = IEMainService.getById(mainId);
				model.add("mainVo",mainVo);
				IESend sendVo = IESendService.getByMainId(mainId);
				model.add("sendVo",sendVo);
				List<IETable> tableList = IETableService.getListByCon(" state = 1 and  mainId = " + mainId+" order by sequence");
				Map<Integer,ArrayList<Integer>> colSpan=new HashMap<Integer,ArrayList<Integer>>(); 
				Map<Integer,ArrayList<Integer>> rowSpan=new HashMap<Integer,ArrayList<Integer>>(); 
				for(int i=0;i<tableList.size();i++){
					IETable ietable=tableList.get(i);
					String secondTh=ietable.getTemplateThTwo();
					if(ietable.getTemplateType() == 6){	//模板六有两个分表头
						String rowThTwo = secondTh.split("!!")[1];
						secondTh = secondTh.split("!!")[0];
						rowSpan.put(i+1, calculateSpanNum(rowThTwo));
					}
					ArrayList<Integer> colsList=calculateSpanNum(secondTh);
					colSpan.put(i+1, colsList);
					ietable.setTemplateThTwo(secondTh.replace(";", ","));
				}
				model.add("colSpan",colSpan);
				model.add("rowSpan",rowSpan);
				model.add("tableList",tableList);
				
				List<IETableSearch> tableSearchList  = IETableSearchService.getListByCon(" state = 1 and  mainId = " + mainId);
				Map<Integer,List<IETableSearch>> tableSearchMap = new HashMap<Integer, List<IETableSearch>>(); //K=tableId   表格对应的指标查询集
				for (IETableSearch vo : tableSearchList) {
					int tableId = vo.getTableId();
					List<IETableSearch> tempList = new ArrayList<IETableSearch>();
					if(tableSearchMap.containsKey(tableId)){
						tempList = tableSearchMap.get(tableId);
					}
					String sql=vo.getSearchSql();
					sql = sql.replace("@","'");
					sql = sql.replace("$", "\"");
					vo.setSearchSql(sql);
					tempList.add(vo);
					tableSearchMap.put(tableId, tempList);
				}
				model.add("tableSearchMap",tableSearchMap);
				Object tools = TemplateMethodFactory.getInstance(VelocityTools.class);
				model.add("tools",tools);
				
				returnUrl = "intemail/editemail";
			}else if("toaddnewemail".equals(state)){ //新增邮件
				List<IETemplateType> templateList = IETemplateTypeService.getListByCon(" state = 1");
				model.add("templateList",templateList);
				
				//DB模板
				List<IRDbConfig> dbList = IRDbConfigService.getAllDb();  //数据库列表
				Map<String,Object> conMap = new HashMap<String,Object>();
				conMap.put("dbList", dbList);
				String main = MyPathUtil.getTemByPath(MyPathUtil.getIEPath()+"dbtemplate.html");
				String dbTemplate  = VelocityTools.render(main, conMap);
				model.add("dbTemplate",dbTemplate);
				model.add("tableCount",0);
				
				returnUrl = "intemail/addemail";
			}else if("iesrcshow".equals(state)){
				int id = ServletRequestUtils.getIntParameter(request, "id", 0);
				if(id == 0){
					return new ContentResult("error");
				}
				String emailCate=ServletRequestUtils.getStringParameter(request, "emailCate","");
				String emailName=ServletRequestUtils.getStringParameter(request, "emailName","");
				List<IEMainSrc> rsList = IEMainSrcService.getSrcTableById(id);
				model.add("rsList",rsList);	
				model.add("emailName",emailName);	
				model.add("emailCate",emailCate);	
				returnUrl = "intemail/ieSrcShow";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return ActionResult.view(returnUrl);
	}

	private ArrayList<Integer> calculateSpanNum(String secondTh) {
		ArrayList<Integer> colsList=new ArrayList<Integer>();
		if(StringUtils.isBlank(secondTh))
			return colsList;
		String[] colGroup=secondTh.split(";");
		for(String s:colGroup){
			colsList.add(s.split(",").length);
		}
		return colsList;
	}
	
	/**自定义邮件数据源查看
	 * @return
	 */
	@Path("manager/iesrc/{state}")
	@Check("mailreport/intemail/manager/show")
	@POST
	@GET
	public ActionResult ieSrcManage (String state){
		HttpServletRequest request = beat.getRequest();
		Model model= beat.getModel();
		String returnUrl ="";
		TemplateMethodFactory.setVelocityContext("tools", VelocityTools.class, model);
		try {
			if("show".equals(state)){  //数据源查询主页面
				String dbName = MyServletRequestUtils.getStringParameter(request, "dbName","");
				String srcTable = MyServletRequestUtils.getStringParameter(request, "srcTable","");
				int pageIndex = MyServletRequestUtils.getIntParameter(request, "pageindex", 1);
				//查询条件组织 以及分页查询参数组织
				StringBuffer where = new StringBuffer(); //查询条件
				StringBuffer url = new StringBuffer(); //分页查询参数
				
				if(!"".equals(dbName)){
					where.append(" and dbName like '%"+dbName+"%'");
					url.append("&dbName="+dbName);
				}
				if(!"".equals(srcTable)){
					where.append(" and  srcTable like '%"+srcTable+"%'");
					url.append("&srcTable="+srcTable);
				}
				
				//分页查询
				Paginate<IEMainSrc> pList = IEMainSrcService.getPagList(where.toString(),pageIndex,20);
				Map<Integer,IESend> sendMap = new HashMap<Integer,IESend>();
				if(pList != null && pList.getItems() != null && pList.getItems().size() > 0){
					
					StringBuffer sb=new StringBuffer("mainId in(");
					for(IEMainSrc main: pList.getItems()){
						sb.append(main.getId()+",");
					}
					sb.deleteCharAt(sb.length()-1);
					sb.append(")");
					List<IESend> sendList=IESendService.getListByCon(sb.toString());
					for(IESend s: sendList){
						if("day".equals(s.getSendFre()))
							s.setSendFre("每天");
						else if("week".equals(s.getSendFre())){
							s.setSendFre("每周一");
						}
						if(s.getState() == 0){
							s.setWarnTel("未调度");
						}else{
							switch(s.getSendState()){
								case 0:s.setWarnTel("未发送");break;
								case 1:s.setWarnTel("成功");break;
								case 2:s.setWarnTel("发送中");break;
								case 3:s.setWarnTel("失败");break;
								case 4:s.setWarnTel("预警");break;
								case 5:s.setWarnTel("待处理");break;
								case 6:s.setWarnTel("等待");break;
								default:s.setWarnTel("未发送");break;
							}
						}
						sendMap.put(s.getMainId(), s);
					}
				}
				model.add("sendMap",sendMap);
				model.add("pList",pList);
				model.add("rsList",pList.getItems());	
				model.add("paramurl",url.toString());
				model.add("dbName",dbName);
				model.add("srcTable",srcTable);
				model.add("pageIndex",pageIndex);
				returnUrl = "intemail/ieSrcManager";
			}else if("changestate".equals(state)){ //状态修改
					int id = ServletRequestUtils.getIntParameter(request, "id", 0);
					int upstate = ServletRequestUtils.getIntParameter(request, "upstate", -1);
					if(id == 0 || upstate == -1){
						return new ContentResult("error");
					}
					IESend sendVo= IESendService.getByMainId(id);
					if (upstate == 0 || upstate == 1) {
						IEMain vo = IEMainService.getById(id);
						vo.setState(upstate);
						sendVo.setState(upstate);
						if (IEMainService.updateVo(vo)&&IESendService.updateVo(sendVo)) {
							return new ContentResult("success");
						} else {
							return new ContentResult("error");
						}
					}else{
						returnUrl = "intemail/testExecute";
					}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return ActionResult.view(returnUrl);
	}
	
	
	/**测试执行，发送邮件
	 * @return
	 */
	@Path("execute")
	@Check("mailreport/intemail/manager/show")
	@POST
	@GET
	public ActionResult execute (){	
		HttpServletRequest request = beat.getRequest();
		Model model= beat.getModel();
		TemplateMethodFactory.setVelocityContext("tools", VelocityTools.class, model);
		try {
		//邮件发送信息
		String[] baseSendInfo = ServletRequestUtils.getStringParameters(request, "baseSendInfo");//收件人/自定义发送频率内容/发送时间/报警短信
		int id = ServletRequestUtils.getIntParameter(request, "id", 0);
		if(baseSendInfo.length != 3 || id ==0){
			return new ContentResult("error");
		}
		IESend send = IESendService.getById(id);
		if(send==null){
			return new ContentResult("error");
		}
		send.setId(id);
		send.setEmailAds(baseSendInfo[1]);
		send.setWarnTel(baseSendInfo[2]);
		send.setWarnMail("zhaoxiang@58.com;huangliang@58.com;guoxue@58.com");
		send.setState(1);
		String serchDate=ServletRequestUtils.getStringParameter(request, "serchDate", null);
		if("".equals(serchDate))
			serchDate=null;
		Timer timer = new Timer();
		timer.schedule(new IESendTimerTask(send,serchDate,true,baseSendInfo[0]), 0);
//		System.out.println("正在执行，请耐心等待，并注意查收邮件!");
		return new MessageResult("正在执行，请耐心等待，并注意查收邮件!");
		} catch (Exception e) {
			e.printStackTrace();
			return new ContentResult("error");
		}
	}
	
	
	/**新增邮件  - 增加表格模板
	 * @return
	 */
	@Path("addnewtable")
	@Check("mailreport/intemail/manager/show")
	@GET
	public ActionResult addNewTable() {
		return new ActionResult() {
			@Override
			public void render(BeatContext beat)  {
				HttpServletRequest request = beat.getRequest();
				HttpServletResponse response = beat.getResponse();		
				int templateType = ServletRequestUtils.getIntParameter(request, "templateType",0);
				int tableRow = ServletRequestUtils.getIntParameter(request, "tableRow",1);
				int tableCol = ServletRequestUtils.getIntParameter(request, "tableCol",1);
				int tableCol2 = ServletRequestUtils.getIntParameter(request, "tableColTwo",1);
				int tableRow2 = ServletRequestUtils.getIntParameter(request, "tableRowTwo",1);
				int tableCount = ServletRequestUtils.getIntParameter(request, "tableCount",1);
				try {
					JSONObject json = new JSONObject();
					
					if(templateType > 0 &&  tableRow > 0 && tableCol > 0 &&  tableCol2 > 0){
						String templateValue = IntEmailMainService.getAddTemplate(templateType, tableRow, tableCol,tableCol2,tableRow2,tableCount);
						json.accumulate("templateValue", templateValue);
						json.accumulate("result", "success");
					}
					response.setContentType("text/plain; charset=utf-8");
					PrintWriter pw = response.getWriter();
					pw.write(json.toString());
					response.flushBuffer();
					IOUtils.closeQuietly(pw);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		};
	}
	
	
	/**添加/更新 新邮件
	 * @return
	 */
	@Path("addNewEmail")
	@Check("mailreport/intemail/manager/show")
	@POST
	@GET
	public ActionResult addNewEmail (){
		HttpServletRequest request = beat.getRequest();
		Model model= beat.getModel();
		TemplateMethodFactory.setVelocityContext("tools", VelocityTools.class, model);
		try {
			//邮件基本信息
			String[] baseMainInfo = ServletRequestUtils.getStringParameters(request, "baseMainInfo");// 邮件名/邮件类别/描述
			if(baseMainInfo.length != 3){
				return new ContentResult("error");
			}
			String creater = spu.getUserByCookie(request).getUsername();
			String creatTime = DateUtil.getStringDate(new Date(), "yyyy-MM-dd HH:mm:ss");
			int state =  ServletRequestUtils.getIntParameter(request, "state",0);
			IEMain baseMain = new IEMain();
			baseMain.setEmailName(baseMainInfo[0]);
			baseMain.setEmailCate(baseMainInfo[1]);
			baseMain.setDescription(baseMainInfo[2]);
			baseMain.setCreater(creater);
			baseMain.setCreatTime(creatTime);
			baseMain.setState(state);
			
			//邮件发送信息
			String[] baseSendInfo = ServletRequestUtils.getStringParameters(request, "baseSendInfo");//收件人/自定义发送频率内容/发送时间/报警短信
			String sendFreValue = ServletRequestUtils.getStringParameter(request, "sendFreValue","");//发送频率
			
			if(baseSendInfo.length != 7 || "".equals(sendFreValue)){
				return new ContentResult("error");
			}
			
			if("self".equals(sendFreValue)){ //自定义
				sendFreValue  = baseSendInfo[1];
			}
			int updateMainId = ServletRequestUtils.getIntParameter(request, "updateMainId",0);//被更新邮件mainId  不为0则代表是更新动作，需删除原来的数据
			IESend send  = new IESend();
			send.setSendFre(sendFreValue);
			send.setEmailAds(baseSendInfo[0]);
			send.setSendTime(baseSendInfo[2]);
			send.setThreshold0(Double.valueOf(baseSendInfo[3]));
			send.setThreshold(Double.valueOf(baseSendInfo[4]));
			send.setWarnTel(baseSendInfo[5]);
			send.setWarnMail(baseSendInfo[6]);
			
			
			send.setState(state);
			if(updateMainId >0 ){
				IESend oldVo = IESendService.getByMainId(updateMainId);
				send.setUpdateTime(oldVo.getUpdateTime());
				send.setSendState(oldVo.getSendState() ==2 ? 0 : oldVo.getSendState());
			}else{
				send.setUpdateTime(DateUtil.getStringDate(new Date(), "yyyy-MM-dd HH:mm:ss"));
				send.setSendState(1);
			}
		
			
			//邮件内容信息
			int tableCount = ServletRequestUtils.getIntParameter(request, "tableCount",0);//表格数
			if(tableCount == 0 ){
				return new ContentResult("error");
			}
			
			String[] templateThs = ServletRequestUtils.getStringParameter(request, "templateThs","").split(";"); //所有表格的th
			String[] templateTwoThs = ServletRequestUtils.getStringParameter(request, "templateTwoThs","").split(";"); //所有表格的2级th
			Map<String,String> thMaps = new HashMap<String,String>();
			Map<String,String> thTwoMaps = new HashMap<String,String>();
			for(int i=0;i<templateThs.length;i++){
				String th =templateThs[i];
				String thTwo=templateTwoThs[i];
				th = th.substring(0,th.lastIndexOf(",")); //去掉最后一个,号
				thTwo = thTwo.substring(0,thTwo.lastIndexOf(","));
				thMaps.put(th.split(":")[0], th.split(":")[1]);
				thTwoMaps.put(th.split(":")[0], thTwo);
			}
			
			String templateTds = ServletRequestUtils.getStringParameter(request, "templateTds",""); //所有表格的td
			Map<String,String> tdMaps = new HashMap<String,String>();
			for(String td : templateTds.split(";")){
				td = td.substring(0,td.lastIndexOf(",")); //去掉最后一个,号
				tdMaps.put(td.split(":")[0], td.split(":")[1]);
			}
			
			String tableNames = ServletRequestUtils.getStringParameter(request, "tableNames",""); //所有表格名
			String tableComments = ServletRequestUtils.getStringParameter(request, "tableComments",""); //所有表格注释
			Map<String,String> tnMaps = new HashMap<String,String>();
			Map<String,String> commentsMaps = new HashMap<String,String>();
			
			for(String tn : tableNames.split(";@")){
				int index = tn.indexOf(":");
				tnMaps.put(tn.split(":")[0], tn.substring(index+1));
			}
			for(String tc : tableComments.split(";@")){
				int index = tc.indexOf(":");
				commentsMaps.put(tc.split(":")[0], tc.substring(index+1));
			}
			
			List<IETable>  tableList = new ArrayList<IETable>();
			Map<IETable,List<IETableSearch>>  contentMap = new HashMap<IETable, List<IETableSearch>>();
			
			for(int i = 1 ; i <= tableCount ; i ++){
				//IETable
				IETable  table = new IETable();
				//模板类型  为0则表示该table没有
				int templateType = ServletRequestUtils.getIntParameter(request, "templateType" + i,0);
				if(templateType == 0 ){
					continue;
				}
				table.setSequence(i);	//表格书顺序
				table.setTemplateType(templateType);
				table.setDataOrder(ServletRequestUtils.getIntParameter(request, "dataOrder" + i,-1));
				//tableName
				table.setTableName(tnMaps.get("tableName"+i));
				table.setTableComment(commentsMaps.get("tableComment"+i));
				//TH
				table.setTemplateTh(thMaps.get("th"+i));
				table.setTemplateThTwo(thTwoMaps.get("th"+i).replaceAll(",\n", ";"));
				//TD   需处理  根据TH的个数来判定TD的切分,分割成每一行的内容,用;号隔开
				String tds = tdMaps.get("td"+i);
				String newTds =  tds.replaceAll(",\n", ";");
				Set<String> tdsSet=getTdsSet(newTds,table.getTemplateThTwo(),templateType); //指标集
				Set<String> serchedTds=new HashSet<String>(); //查询的指标集
				//检查表格单元格是够为空、重复
				String checkMsg=checkTable(templateType,thMaps.get("th"+i),table.getTemplateThTwo(),newTds);
				if(checkMsg != null){
					return new MessageResult("表格"+i+","+checkMsg);
				}
				
				table.setTemplateTd(newTds);
				table.setState(1);
				
				//tableSearch
				List<IETableSearch>  tableSearchList = new ArrayList<IETableSearch>();
				
				//Index
				String[] templateIndexs = ServletRequestUtils.getStringParameters(request, "templateIndex" + i);
				//Sql
				String[] templateSqls = ServletRequestUtils.getStringParameters(request, "templateSql" + i);
				//DB
				String[] templateDBs = ServletRequestUtils.getStringParameters(request, "templateDB" + i);
				//数据源表 srcTables
				String[] srcTables = ServletRequestUtils.getStringParameters(request, "srcTable" + i);
				//数据源表的日期字段
				String[] dateFields = ServletRequestUtils.getStringParameters(request, "dateField" + i);
				//是否校验数据源 checkTable
				int[] checkTables = ServletRequestUtils.getIntParameters(request, "checkTable" + i);
				
				if((templateIndexs.length == templateSqls.length) &&  (templateSqls.length  == templateDBs.length)){
					Set<String> evalTdSet=getEvalTdSet(templateIndexs,templateSqls); //获得计算式指标集
					for(int j = 0 ; j < templateIndexs.length ; j ++){
						IETableSearch  tableSearch = new IETableSearch();
						
						String index = templateIndexs[j].trim();
						String sql = templateSqls[j].trim();
						checkMsg = checkSQL(templateType,index, sql,tdsSet,serchedTds,evalTdSet);
						if(checkMsg != null)
							return new MessageResult("表格"+i+",第"+(j+1)+"条查询"+checkMsg);

						//sql中的一些特殊符号要处理 
						sql = sql.replace("'", "@");
						sql = sql.replace("`", "");
						sql = sql.replace("\"", "$");
						
						int dbId = Integer.parseInt(templateDBs[j].split(":")[1]);
						String dbName = templateDBs[j].split(":")[0];
						String hostName = templateDBs[j].split(":")[2];
						tableSearch.setIndexs(index);
						tableSearch.setSearchSql(sql);
						tableSearch.setDbId(dbId);
						tableSearch.setDbHost(hostName);
						tableSearch.setDbName(dbName);
						tableSearch.setSrcTable(srcTables[j].trim());
						tableSearch.setDateField(dateFields[j].trim());
						tableSearch.setIsCheck(checkTables[j]);
						
						if(!StringUtils.containsIgnoreCase(sql, "select")){
							tableSearch.setIsSpe(1);
						}else{
							tableSearch.setIsSpe(0);
						}
						tableSearch.setState(1);
						
						tableSearchList.add(tableSearch);
					}
				}else{
					return new ContentResult("error");
				}
				if(!serchedTds.containsAll(tdsSet))
					return new MessageResult("表格"+i+"有指标未查询,请检查!");
				contentMap.put(table, tableSearchList);
				tableList.add(table);
//				System.out.println("表格"+i+"名："+table.getTableName());
			}
			
			//插入数据
			int newMainId=IntEmailMainService.addNewEmail(baseMain, send, tableList ,contentMap);
			if(newMainId == 0){
				return new ContentResult("error");
			}
			
//			System.out.println("更新邮件ID："+updateMainId);
			if(updateMainId > 0 ){
				ieAddService.updateMainId(updateMainId, newMainId);
				 IntEmailMainService.deleteOldEmail(updateMainId);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return new ContentResult("error");
		}
		
		return new ContentResult("success");
	} 

	/**
	 * 获得一个表格的计算式指标集
	 * @param templateIndexs
	 * @param templateSqls
	 * @return
	 */
	private Set<String> getEvalTdSet(String[] templateIndexs,String[] templateSqls) {
		Set<String> evalTdSet=new HashSet<String>();
		for(int i=0;i<templateSqls.length;i++){
			String sql=templateSqls[i];
			String indexs[] = templateIndexs[i].split("/");
			if(!sql.toLowerCase().contains("select")){
				for(String tmp : indexs)
					evalTdSet.add(tmp.split(":")[0]);
			}
		}
		return evalTdSet;
	}



	private String checkTable(int templateType,String templateThs,String templateThTwos,String templateTds){
		String checkMsg=checkTh(templateThs);
		if(checkMsg != null)
			return checkMsg;
		checkMsg=checkTh(templateThTwos);
		if(checkMsg != null)
			return checkMsg;
		checkMsg=checkTd(templateType,templateTds,templateThs,templateThTwos);
		if(checkMsg != null)
			return checkMsg;
		return null;
	}
	
	
	//检查单元格
	private String checkTd(int templateType,String templateTds,String templateThs,String templateThTwos){
		List<String> excludeTh =  new ArrayList<String>();
		String[] thStrings = {"昨日","日比","上周","周比"};
		excludeTh = Arrays.asList(thStrings);
		if(templateTds==null || templateTds.length()==0)
			return null;
		String[] rowTds=templateTds.split(";"); //分割成每一行
		String[] ths=templateThs.split(",");
		String[] thTwos=templateThTwos.replace(";", ",").split(",");
		if(templateType==5)
			return checkTd5(rowTds,ths,templateThTwos);
		if(templateType==6)
			return checkTd6(rowTds,templateThTwos);
		for(String tmp:rowTds){
			Set<String> tdSet=new HashSet<String>();
			String[] tds=tmp.split(",");	//分割成每一格
			for(int i=0;i<tds.length;i++){
				if(i==0){
					if(tds[i].startsWith("null"))
						return "行表头不能为空!";
					if(templateType==2 && (!tds[i].contains("#") || tds[i].endsWith("#")))
						return "行表头格式必须是 '指标#标记' 的形式!";
					continue;
				}
				if(!tds[i].equals("null") && tdSet.contains(tds[i])){
					return "行单元格标记不得重复!";
				}
				if(i>0 && !tds[i].equals("null")){
					if(templateType==1 && excludeTh.contains(ths[i]))
						return "日比、周比对应的列必须为空!";
					if((templateType==3 || templateType==4) && excludeTh.contains(thTwos[i-1]))
						return "日比、周比对应的列必须为空!";
				}
				if(i>0 && tds[i].equals("null")){
					if(templateType==1 && !excludeTh.contains(ths[i]))
						return "指标列不能为空!";
					if((templateType==3 || templateType==4) && !excludeTh.contains(thTwos[i-1]))
						return "指标列不能为空!";
				}
				tdSet.add(tds[i]);
			}
		}
		return null;
	}

	//检查模板5单元格
	private String checkTd5(String[] rowTds, String[] ths,String templateThTwos){
		String[] thTwos=templateThTwos.split(";");
		int n=0;
		int rowNum=0;
		HashMap<Integer,Integer> numMap = new HashMap<Integer,Integer>();
		for(int i=0;i<rowTds.length;i++){
			numMap.put(i,rowNum);
			if(i == rowNum)
				rowNum=rowNum + thTwos[n++].split(",").length;
		}
		for(int j=0;j<rowTds.length;j++){
			Set<String> tdSet=new HashSet<String>();
			String[] tds=rowTds[j].split(",");	//分割成每一格
			for(int i=0;i<tds.length;i++){
				if(i==0 || (i==1 && j == numMap.get(j))){
					if(tds[i].startsWith("null"))
						return "行表头不能为空!";
				}
				if(i==0 || i==1)
					continue;
				if(!tds[i].equals("null") && tdSet.contains(tds[i])){
					return "行单元格标记不得重复!";
				}
				if(i>0 && !tds[i].equals("null")){
					if(j != numMap.get(j) && (ths[i].equals("日比")||ths[i].equals("周比")))
						return "日比、周比对应的列必须为空!";
					if(j == numMap.get(j) && (ths[i-1].equals("日比")||ths[i-1].equals("周比")))
						return "日比、周比对应的列必须为空!";
				}
				if(i>0 && tds[i].equals("null")){
					if(j != numMap.get(j) && !(ths[i].equals("日比")||ths[i].equals("周比")))
						return "指标列不能为空!";
					if(j == numMap.get(j) && !(ths[i-1].equals("日比")||ths[i-1].equals("周比")))
						return "指标列不能为空!";
				}
				tdSet.add(tds[i]);
			}
		}
		return null;
	}


	//检查模板5单元格
	private String checkTd6(String[] rowTds, String templateThTwos){
		String[] thTwos = templateThTwos.split("!!")[1].split(";");
		String[] ths = templateThTwos.split("!!")[0].replace(";", ",").split(",");
		int n=0;
		int rowNum=0;
		HashMap<Integer,Integer> numMap = new HashMap<Integer,Integer>();
		for(int i=0;i<rowTds.length;i++){
			numMap.put(i,rowNum);
			if(i == rowNum)
				rowNum=rowNum + thTwos[n++].split(",").length;
		}
		for(int j=0;j<rowTds.length;j++){
			Set<String> tdSet=new HashSet<String>();
			String[] tds=rowTds[j].split(",");	//分割成每一格
			for(int i=0;i<tds.length;i++){
				if(i==0 || (i==1 && j == numMap.get(j))){
					if(tds[i].startsWith("null"))
						return "行表头不能为空!";
				}
				if(i==0 || i==1)
					continue;
				if(!tds[i].equals("null") && tdSet.contains(tds[i])){
					return "行单元格标记不得重复!";
				}
				if(i>0 && !tds[i].equals("null")){
					if(j != numMap.get(j) && (ths[i-1].equals("日比")||ths[i-1].equals("周比")))
						return "日比、周比对应的列必须为空!";
					if(j == numMap.get(j) && (ths[i-2].equals("日比")||ths[i-2].equals("周比")))
						return "日比、周比对应的列必须为空!";
				}
				if(i>0 && tds[i].equals("null")){
					if(j != numMap.get(j) && !(ths[i-1].equals("日比")||ths[i-1].equals("周比")))
						return "指标列不能为空!";
					if(j == numMap.get(j) && !(ths[i-2].equals("日比")||ths[i-2].equals("周比")))
						return "指标列不能为空!";
				}
				tdSet.add(tds[i]);
			}
		}
		return null;
	}
	
	//检查表头
	private String checkTh(String templateThs){
		if(templateThs==null || templateThs.equals("null"))
			return null;
		String[] ths=templateThs.split(","); //分割
			Set<String> thSet=new HashSet<String>();
			for(int i=0;i<ths.length;i++){
				if(ths[i].startsWith("null")){
					return "表头不能为空!";
				}
				thSet.add(ths[i]);
			}
		return null;
	}

	//获得一个表的td 指标集合
	private Set<String> getTdsSet(String templateTds,String templateThTwos,int templateType){
		if(templateType == 5 || templateType == 6)
			return getTdsSet5(templateTds,templateThTwos,templateType);
		int rowspan=templateThTwos.split(",").length;
		Set<String> tdSet=new HashSet<String>();
		String[] rowTds=templateTds.split(";"); //分割成每一行
		for(int j=0;j<rowTds.length;j++){
			String[] tds=rowTds[j].split(",");	//分割成每一格
			for(int i=0;i<tds.length;i++){
				if("null".equals(tds[i]))
					continue;
				if(templateType==5 && j%rowspan==0 && i==1)
					continue;
				if(i==0){ //第一格
					if(templateType==2 && tds[i].indexOf("#")>0 && !tds[i].endsWith("#"))
						tdSet.add(tds[i].split("#")[1]);
				}else
					tdSet.add(tds[i]);
			}
		}
		return tdSet;
	}
	
	//获得一个模板5 表的td 指标集合
	private Set<String> getTdsSet5(String templateTds,String templateThTwos,int templateType){
		String[] rowTds=templateTds.split(";"); //分割成每一行
		String[] thTwos= null;
		if(templateType == 6){
			thTwos = templateThTwos.split("!!")[1].split(";");
		}else{
			thTwos=templateThTwos.split(";");
		}
		int n=0;
		int rowNum=0;
		HashMap<Integer,Integer> numMap = new HashMap<Integer,Integer>();
		for(int i=0;i<rowTds.length;i++){
			numMap.put(i,rowNum);
			if(i == rowNum)
				rowNum=rowNum + thTwos[n++].split(",").length;
		}
		
		Set<String> tdSet=new HashSet<String>();
		for(int j=0;j<rowTds.length;j++){
			String[] tds=rowTds[j].split(",");	//分割成每一格
			for(int i=0;i<tds.length;i++){
				if("null".equals(tds[i]))
					continue;
				if(i==0 || (j == numMap.get(j) && i==1))
					continue;
				else
					tdSet.add(tds[i]);
			}
		}
		return tdSet;
	}
	
	//检查sql
	private String checkSQL(int templateType,String index,String sql,Set<String> tdSet,Set<String> serchedTds,Set<String> evalTdSet){
		String serchIndex[]=index.split("/");
		String serchSQL[] = sql.split(";");
		//计算式指标
		if(!sql.toLowerCase().contains("select") && serchIndex.length != serchSQL.length){
				return "指标和计算公式数目不相等!";
		}
		
		Set<String> tmpSet=new HashSet<String>();
		for(int i=0; i < serchIndex.length; i++){
			String tmp[]=serchIndex[i].split(":");
			String s=tmp[0];
			if(tmpSet.contains(s)||(templateType==2 &&serchedTds.contains(s)))
				return "查询指标集重复!";
			if(!tdSet.contains(s))
				return "有指标集不在单元格标记内!";
			tmpSet.add(s);
			serchedTds.add(s);
			
			if(!sql.toLowerCase().contains("select")){
				if(tmp.length!=2)
					return "计算式指标填写格式为 '指标:类型',1表示百分比,0表示非百分比!";
				String type=tmp[1].trim();
				if(!(type.equals("0")||type.equals("1")||type.equals("2")))
					return "计算式指标类型为：0-非百分比，1-百分比!";
				if(MathEval.checkIndexBySelfRow(sql, tdSet)==false)
					return "指标计算式有误!";
				if(MathEval.checkEvalTd(sql, evalTdSet))
					return "计算公式中不得包含任何计算式指标!";
			}
		}
//		//计算式指标
//		if(!sql.toLowerCase().contains("select")){
//			if(!(serchIndex.length == serchSQL.length))
//				return "指标和计算公式数目不相等!";
//			String tmp[]=index.split(":");
//			if(tmp.length!=2)
//				return "计算式指标填写格式为 '指标:类型',1表示百分比,0表示非百分比!";
//			String type=tmp[1].trim();
//			if(!(type.equals("0")||type.equals("1")))
//				return "计算式指标类型为：0-非百分比，1-百分比!";
//			if(MathEval.checkIndexBySelfRow(sql, tdSet)==false)
//				return "指标计算式有误!";
//			if(MathEval.checkEvalTd(sql, evalTdSet))
//				return "计算公式中不得包含任何计算式指标!";
//			
//		}
			
		return null;
	}
	
	private class MessageResult extends ActionResult{
		private String message=null;
		public MessageResult(String msg){
			this.message=msg;
		}
		@Override
		public void render(BeatContext beat)  {
			HttpServletResponse response = beat.getResponse();		
			try {
				response.setContentType("text/plain; charset=utf-8");
				PrintWriter pw = response.getWriter();
				pw.write(message);
				response.flushBuffer();
				IOUtils.closeQuietly(pw);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
}
